<?php

/**
 * SQL filtering class
 * Resposible for handling data grids (column sorting, pagination, filtering, etc)
 *
 * @author Ben Rowe
 */
class Filter
{
	
	/**
	 * Stores the name of this filter. If a name is provided the settings will 
	 * be stored as sessions.
	 *
	 * @access private
	 * @var string
	 */
	var $_name = null;
	
	/**
	 * Stores the ordering commands for setting up column ordering
	 * Each order element is it's self an array, comprising of a field
	 * & direction key
	 * 
	 * @access private
	 * @var array
	 */
	var $_orders = array();
	
	/**
	 * An array of conditions to be addded onto the filter query
	 *
	 * @access private
	 * @var array
	 */
	var $_conditions = array();
	
	/**
	 * Unknown, this is currently not implemented
	 *
	 * @access private
	 * @var boolean
	 */
	var $_limit = false;
	
	/**
	 * Records Per page
	 * Sets the number of data records to return per page
	 * 
	 * @access private
	 * @var integer
	 */
	var $_rpp	= 10;
	
	/**
	 * The page the filter is currently on
	 * 
	 * @access private
	 * @var integer
	 */
	var $_page = 1;
	
	/**
	 * Number of pages currently available
	 *
	 * @access private
	 * @var integer
	 */
	var $_pages	= 1;
	
	/**
	 * Number of records currently available
	 *
	 * @access private
	 * @var integer
	 */
	var $_total_records = 0;
	
	/**
	 * Stores the alias of the FROM table
	 * The provided query MUST contain an alias for the primary table
	 *
	 * @access private
	 * @var string
	 */
	var $_alias = null;
	
	/**
	 * Key/Value array of conditions applied to main query
	 * (used by the view)
	 *
	 * @access private
	 * @var array
	 */
	var $_filter = array();
	
	/**
	 * Stores the data created by the filter query
	 *
	 * @access private
	 * @var array
	 */
	var $_data = array();
	
	/**
	 * Stores the SQL that gets processed
	 *
	 * @access private
	 * @var string
	 */
	var $_sql;
	
	/**
	 * Constructor
	 * Set the default ordering of the Filter object
	 * 
	 * @access public
	 * @param string $order_by sql field to order by
	 * @param string $order_dir order direction, either ASC or DESC
	 * @return Filter
	 */
	function Filter($order_by = false, $order_dir = 'ASC')
	{
		if(is_string($order_by)) {
			$this->clearOrder();
			$this->addOrder($order_by, $order_dir);
		}
	}
	
	/**
	 * Attempt to build filter based on request variables
	 * Looks for the filter, page, sort_by & sort_dir variables from _REQUEST and tries to figure out what should be included
	 * 
	 * @access public
	 * @return null
	 */
	function autoFilter()
	{
		//$this->clearOrder();// clear any previously set ordering?
		
		// lets get the page we're currently on
		$this->setPage($this->_getPage());

		$sort_by = $this->_getSortBy();
		$sort_dir = $this->_getSortDir();
		if(!empty($sort_by)) {
			$this->clearOrder();
			$this->addOrder($sort_by, $sort_dir);
		}
		if(isset($_REQUEST['filter']) && is_array($_REQUEST['filter'])) {
			foreach($_REQUEST['filter'] as $field=>$value) {
				if(!empty($value)) {
					$this->_filter[$field] = $value;
					$this->addCondition($field, '%'.$value.'%', 'LIKE');
				}
			}
		}
	}
	
	/**
	 * Set the name of this filter
	 *
	 * @access public
	 * @param string $name
	 * @return boolean
	 */
	function setName($name)
	{
		$this->_name = $name;
		return true;
	}
	
	/**
	 * Set the maximum number of records to return for this filter request (pagination)
	 *
	 * @access public
	 * @param int $rpp records per page
	 * @return boolean
	 */
	function setRecordsPerPage($rpp)
	{
		$this->_rpp = $rpp;
		return true;
	}
	
	/**
	 * Set the current page. It will be used to calculate the offset for the sql query
	 *
	 * @access public
	 * @param int $page current page
	 * @return unknown
	 */
	function setPage($page)
	{
		if($page < 1) $page = 1;
		$this->_page = $page;
		return true;
	}
	

	/**
	 * Set the query to be used
	 * The provided SQL must be defined in a stict format.
	 * All fields must be encased in backticks, all tables must have alias's
	 * ALL SQL commands (select, from, where, group, inner join, etc) must be 
	 * in UPPER CASE
	 *
	 * @access public
	 * @param string $sql
	 * @return boolean
	 */
	function setQuery($sql)
	{
		$this->_sql	= $sql;
		$this->_alias = $this->_getAlias();
		$this->parse();
		return true;
	}
	
	/**
	 * Run the filter
	 * Builds the nessacary sql to get the total number of records,
	 * then builds the primary sql, runs it and retrieves the results.
	 * 
	 * @access public
	 * @return boolean
	 */
	function parse()
	{
		// get the number of TOTAL record available
		$sql = $this->_buildCountSQL();
		$result	= mysql_query($sql) or die(mysql_error());
		$this->_totalRecords((int)mysql_result($result, 0, 0));
		
		// get the data
		$sql	= $this->_buildSQL();
		$result	= mysql_query($sql) or die(mysql_error().'<br><br>'.$sql);
		while($row = mysql_fetch_array($result)) {
			$results[] = $row;
		}
		$this->_data = isset($results) ? $results : false;
		return true;
	}
	
	/**
	 * Set the total number of records found for the base query + calculate the number of pages, etc
	 *
	 * @access private
	 * @param int $total_records
	 * @return boolean
	 */
	function _totalRecords($total_records)
	{
		$this->_total_records = $total_records;
		$this->_pages = ceil($total_records / $this->_rpp);
		if($this->_page > $this->_pages) $this->_page = $this->_pages;
	}
	
	/**
	 * Builds the main SQL string for executing
	 *
	 * @access private
	 * @return string
	 */
	function _buildSQL()
	{
		$sql		= $this->_sql;
		$where		= $this->getConditions();
		$order		= $this->getOrders();
		$limit		= $this->getLimit();
		$find		= array('%-w-%', '%-o-%', '%-l-%');
		$replace	= array($where, $order, $limit);
		$sql		= str_replace($find, $replace, $sql); // replace %w with the conditions and remove the other filter params
		return $sql;
	}
	
	/**
	 * Build the sql for getting the number of total records for the main query.
	 * Essentially it replaces the select fields with a count(*)
	 *
	 * @access private
	 * @return string
	 */
	function _buildCountSQL()
	{
		$sql		= $this->_sql;
		$where		= $this->getConditions(false);
		$find		= array('%-w-%', '%-o-%', '%-l-%');
		$replace	= array($where, null, null);
		if(preg_match("/SELECT (.*) FROM/", $sql, $_matches)) {
			$find[] = $_matches[1];
			$replace[] = 'COUNT(*)';
		}
		$sql	= str_replace($find, $replace, $sql); // replace %w with the conditions and remove the other filter params
		return $sql;
	}
	
	/**
	 * Add a new conditional value into the conditions stack (used when generating the filter queries later on)
	 *
	 * @access public
	 * @param string $field
	 * @param mixed $value
	 * @param string $operator
	 * @return boolean
	 */
	function addCondition($field, $value, $operator = '=')
	{
		$this->_conditions[] = array('field' => $field, 'value' => $value, 'operator' => $operator);
		return true;
	}
	
	/**
	 * Build the sql for the conditions of the filter
	 * 
	 * @access private
	 * @return string
	 */
	function buildConditions()
	{
		if(count($this->_conditions) > 0) {
			$sql	= array();
			foreach($this->_conditions as $condt) {
				$sql[]	= '`'.$condt['field'].'` '.$condt['operator'].' '.$this->_value($condt['value']);
			}
			//$this->_conditions = $sql;
			return "WHERE ".implode(' AND ', $sql);
		}
		return null;
	}
	
	/**
	 * Public wrapper for the buildConditions method
	 * 
	 * @access public
	 * @return string
	 */
	function getConditions()
	{
		return $this->buildConditions(); 
	}
	
	/**
	 * Build the order SQL
	 *
	 * @access public
	 * @return string
	 */
	function buildOrder()
	{
		$order = null;
		if($this->hasOrder()) {
			$order = ' ORDER BY ';
			
			foreach($this->_orders as $orders) {
				$parts[] = $this->_buildOrderField($orders['field']) .' '.$orders['direction'];
			}
			$order .= implode(',', $parts);
		}
		return $order;
	}
	
	/**
	 * Builds a semantically correct field name for the 'order by' part of the query
	 *
	 * @access private
	 * @param string $field
	 * @return string
	 */
	function _buildOrderField($field)
	{
		$field = str_replace('`', '', $field);
		if(strpos($field, '.') === false) {
			// this field does not contain any alias! lets use the default
			$field = $this->_alias .'.'.$field;
		}
		// place backticks around the alias & field name
		return preg_replace("/^(\w+)\.(\w+)/i", "`$1`.`$2`", $field);
	}
	
	/**
	 * Retrieve the primary table's alias from the from part of the provided
	 * query
	 * 
	 * @access private
	 * @return string
	 */
	function _getAlias()
	{
		if(preg_match("/FROM `(?:\w+)` `(\w+)`/", $this->_sql, $_match)) {
			return $_match[1];
		}
		trigger_error('The following query does not contain a properlly formatted query string!<br />'.$this->_sql.'<br /><br />The query must contain upper case commands, table names & table alias\'s, encased in backticks.', E_USER_ERROR);
	}
	
	/**
	 * Alias for buildOrder (for consistancy)
	 *
	 * @access public
	 * @return string
	 */
	function getOrders()
	{
		return $this->buildOrder();
	}
	
	/**
	 * Checks to see if at least one order condition has been set
	 *
	 * @access public
	 * @return boolean
	 */
	function hasOrder() 
	{
		return (count($this->_orders) > 0);
	}
	
	/**
	 * build the limit part of the sql
	 *
	 * @access public
	 * @return string
	 */
	function buildLimit()
	{
		if(is_int($this->_rpp)) {
			// a valid limit is in place
			$rpp	= $this->_rpp;
			$offset = (($this->_page - 1) * $rpp);
			if($offset < 0) $offset = 0;
			return ' LIMIT '.$offset.', '. $rpp.' ';
		}
		return null;
	}
	
	/**
	 * Alias for buildLimit (for consistancy)
	 *
	 * @access public
	 * @return string
	 */
	function getLimit()
	{
		return $this->buildLimit();
	}
	
	/**
	 * Prepare an unknown value for being inserted into a database query, based on the type it may or may not be encased within single quotes.
	 *
	 * @param mixed $value
	 * @return string
	 */
	function _value($value)
	{
		if(is_numeric($value)) {
			return ''.$value.'';
		} elseif(is_string($value)) {
			return '\''.$this->_toString($value).'\'';
		} elseif(is_null($value)) {
			return 'NULL';
		} elseif(is_bool($value)) {
			return $value ? 'true' : 'false';
		}
		return null;
	}
	
	/**
	 * Prepare a string (value) for being inserted into a database query
	 * special characters such as new lines, tabs, etc are all converted so they arn't parsed
	 *
	 * @param string $value
	 * @return string
	 */
	function _toString($value)
	{
		$search = array("\\","\r","\n","\t","'",'"');
		$replace = array("\\\\",'\r','\n','\t','\'','\"');
		return str_replace($search, $replace, $value);		
	}
	
	
	/**
	 * Add an order field and direction
	 *
	 * @access public
	 * @param string $field
	 * @param string $direction
	 * @return boolean
	 */
	function addOrder($field, $direction = 'ASC')
	{
		$direction = strtoupper($direction);
		$direction = ($direction == 'DESC' ? 'DESC' : 'ASC'); // force direction to either be ASC or DESC
		$this->_orders[] = array('field' => $field, 'direction' => $direction);
		return true;
	}
	
	/**
	 * Clear all stored orders from the order hash
	 * 
	 * @access public
	 * @return boolean 
	 */
	function clearOrder()
	{
		$this->_orders = array();
		return true;
	}
	
	/**
	 * Generate an array of data about the filter that can be used for view specific purposes.
	 * 
	 * @access public
	 * @return object
	 */
	function getReport()
	{
		$return					= new stdClass;
		$return->rpp			= $this->_rpp;
		$return->page			= $this->_page;
		$return->pages			= $this->_pages;
		$return->total_records	= $this->_total_records;
		$return->filter			= $this->_filter;
		$return->sort			= $this->_orders;
		
		
		return $return;
	}
	
	/**
	 * retrieve the data generated by the tiler
	 *
	 * @access public
	 * @return array
	 */
	function getData()
	{
		return $this->_data;
	}
	
	/**
	 * Attempts to get the page from
	 *
	 * @access private
	 * @return int
	 */
	function _getPage()
	{
		return isset($_REQUEST['page']) ? (int)$_REQUEST['page'] : 1;
	}
	
	/**
	 * Attempts to get the sort field as part of the auto filter functionality
	 * 
	 * @access private
	 * @return string on success
	 */
	function _getSortBy()
	{
		return isset($_REQUEST['sort_by']) ? $_REQUEST['sort_by'] : false;
	}
	
	/**
	 * Attempts to get the sort direction as part of the auto filter
	 * If it can't find anything it returns ASC
	 *
	 * @access private
	 * @return string
	 */
	function _getSortDir()
	{
		if(isset($_REQUEST['sort_dir'])) {
			return (strtolower($_REQUEST['sort_dir']) == 'desc') ? 'DESC' : 'ASC';
		}
		return 'ASC';
	}
}

?>
